var sqlite3 = require("sqlite3").verbose();
var dbName = "Food.sqlite";
var database = {
    queryTableInfo: function( tableName,filter, succeed, failed, scope) {
        if (filter) {
            filter = " WHERE " + filter;
        } else {
            filter = "";
        }
        var db = new sqlite3.Database(dbName);
        db.serialize(function() {
            var sqlStr = "SELECT * FROM " + tableName + " " + filter;
            db.all(sqlStr, function(err, rows) {
                if (!err && rows && rows.length > 0) {
                    succeed.call(scope || this, err, rows);
                    console.log("query sessuce!");
                } else {
                    failed.call(scope || this, err, rows);
                    console.log("query failed!");
                }
            });
        });
        db.close();
    },
    queryAllMemberInfo:function(succeed,failed,scope){
        var table="member",filter="";
        this.queryTableInfo(table,filter,succeed,failed,scope);
    },
    querySomeMemberInfo:function(name,succeed,failed,scope){
        var table="member",filter="name = '" + name + "'";
        this.queryTableInfo(table,filter,succeed,failed,scope);
    },
    checkIsManager:function(manager,word,succeed,failed,scope){
        var table="manage",filter="manager = '" + manager + "' AND word = '" + word + "'";
        this.queryTableInfo(table,filter,succeed,failed,scope);
    },
    aaShare: function(costInfo
        /*examples:{memberInfo:[{id:1,name:yan,money:100,num:2},{id:2,name:deng,money:100,num:1},{id:3,name:liu,money:100,num:1}],cost:88,datetime:"****-**-**"}*/
        , callback, scope) {
        var db = new sqlite3.Database(dbName);
        db.serialize(function() {
            var memberInfo = costInfo.memberInfo,
                count = 0,
                length = memberInfo.length,
                cost = Number(costInfo.cost),
                consumeInfo="";;
            for (var i = 0; i < length; i++) {
                var user = memberInfo[i];
                var num=Number(user.num) || 0
                count += num;
                if(i===0){
                    consumeInfo+=user.name;
                }else{
                    consumeInfo+="、"+user.name;
                }
                if(num>1){
                    consumeInfo+="*"+num;
                }
            }
            if(length>0){
                if(cost>0){
                    consumeInfo+=",共消费￥："+cost;
                }else{
                    consumeInfo+=",充值￥："+(-cost);
                }
            }
            var average = cost / count;
            var updateStr = "UPDATE member SET money = ? WHERE id = ?";
            var updateMdone = false,updateSdone = false,counter=0;
            for (var j = 0;j < length; j++) {
                var user = memberInfo[j];
                var money=Number(user.money)||0;
                var num=Number(user.num)||0;
                var balance = money - average*num;
                db.run(updateStr, balance, user.id, function() {
                    if (++counter === length) {
                        updateMdone = true;
                        if (updateSdone) {
                            db.close();
                        }
                    }
                });
            }
            db.get("SELECT * FROM sqlite_sequence WHERE name = 'change'", function(err, row) {
                console.log("select sequence end");
                if (row) {
                    var times = Number(row.seq);
                    db.run("UPDATE sqlite_sequence SET seq = ? WHERE name = 'change'", ++times, function() {
                        updateSdone = true;
                        if (updateMdone) {
                            db.close();
                        }
                        if (!err) {
                            callback.call(scope || this,consumeInfo);
                        }
                    });
                }
            });
        });
    },
    addMember: function(name, money, callback, scope) {
        var db = new sqlite3.Database(dbName);
        db.get("SELECT * FROM sqlite_sequence WHERE name = ?", "member", function(err, row) {
            if (row) {
                var count = Number(row.seq) || 0;
                db.run("INSERT INTO member VALUES (?,?,?)", ++count, name, Number(money) || 0, function(err) {
                    if (!err) {
                        callback.call(scope || this);
                    }
                    db.close();
                });
            }
        });
    },
    logConsumption: function() {

    }
}

module.exports = database;